package com.guan.study.poi.read;

import com.guan.study.easyExcelDemo.Student;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * @Date: 2023/7/2 10:16
 * @Author: yaobai
 * @Description:
 */
@RequestMapping("/readExcel03")
@RestController
@Log4j2
public class Read01 {

    @PostMapping("/import/user")
    public void importUserExcel(@RequestBody MultipartFile file) throws Exception {

        List<Student> students = importExcel(file);
        students.stream().forEach(s -> System.out.println(s));

    }

    public static List<Student> importExcel(MultipartFile file) throws Exception {

        List<Student> students = new ArrayList<>();

        try (FileInputStream fis = (FileInputStream) file.getInputStream()){
             Workbook workbook = new XSSFWorkbook(fis);

            Sheet sheet = workbook.getSheetAt(0); // 假设导入的数据在第一个工作表中

            for (Row row : sheet) {
                int rowNum = row.getRowNum();
                if (rowNum == 0) {
                    continue; // 跳过标题行
                }

                Cell nameCell = row.getCell(0);
                Cell ageCell = row.getCell(1);
                Cell addrCell = row.getCell(2);
                Cell schoolCell = row.getCell(3);
                Cell chineseCell = row.getCell(4);
                Cell mathCell = row.getCell(5);
                Cell englishCell = row.getCell(6);

                String name = getValueAsString(nameCell);
                Integer age = getValueAsInteger(ageCell);
                String addr = getValueAsString(addrCell);
                String school = getValueAsString(schoolCell);
                String chinese = getValueAsString(chineseCell);
                String math = getValueAsString(mathCell);
                String english = getValueAsString(englishCell);

                // 校验数据合法性
                List<String> errors = validateStudentData(name, age, addr, school, chinese, math, english, students);

                if (errors.isEmpty()) {
                    students.add(new Student(name, age, addr, school, chinese, math, english));
                } else {
                    for (String error : errors) {
                        System.out.println("导入失败，行：" + (rowNum + 1) + "，原因：" + error);
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        return students;
    }

    public static String getValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue().trim();
    }

    public static Integer getValueAsInteger(Cell cell) {
        if (cell == null) {
            return null;
        }
        cell.setCellType(CellType.STRING);
        try {
            return Integer.parseInt(cell.getStringCellValue());
        } catch (NumberFormatException e) {
            return null;
        }
    }

    public static List<String> validateStudentData(String name, Integer age, String addr,
                                                   String school,String chinese, String math,
                                                   String english, List<Student> students) {
        List<String> errors = new ArrayList<>();

        if (StringUtils.isBlank(name)){
            errors.add("姓名不能为空");
        }else  if(name.length() > 10) {
            errors.add("姓名长度超过10个字符");
        }

        if (isDuplicateName(name, students)) {
            errors.add("姓名重复");
        }

        if (age != null && (age < 0 || age > 99)) {
            errors.add("年龄超出范围");
        }

        if (addr.length() > 30) {
            errors.add("地址长度超过30个字符");
        }

        if (school.length() > 15) {
            errors.add("学校名称长度超过15个字符");
        }

        // 校验成绩格式是否合法
        if (!isValidScore(chinese)) {
            errors.add("语文成绩格式不合法");
        }
        if (!isValidScore(math)) {
            errors.add("数学成绩格式不合法");
        }
        if (!isValidScore(english)) {
            errors.add("英语成绩格式不合法");
        }

        return errors;
    }

    public static boolean isDuplicateName(String name, List<Student> students) {
        // 存储已导入的姓名，用于检查重复
        Set<String> importedNames = new HashSet<>();
        // 假设在students中已经存储了之前导入成功的学生数据
        for (Student student : students) {
            importedNames.add(student.getName());
        }
        return importedNames.contains(name);
    }

    public static boolean isValidScore(String score) {
        try {
            double value = Double.parseDouble(score);
            // 保留1位小数
            return value >= 0 && value <= 100;
        } catch (NumberFormatException e) {
            return false;
        }
    }

}
